﻿/*
=============================================================================
CreatedTime:2011-10-28
Description:Tb_comment三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Models;//引入模型层
namespace DAL
{
	public  partial class Tb_commentServiceBase
	{
		
		#region 表是否存在该记录
		/// <summary>
		/// Tb_comment表是否存在该记录
		/// </summary>
		/// <param name="id"></param>
        /// <returns></returns>
		public bool ExistsTb_comment(int id)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("Select count(1) from Tb_comment");
			strSql.Append(" where id = @id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", id)
				};
			return DBHelper.Exists(strSql.ToString(),parameters);
		}	
		#endregion
		#region 表增加一条数据
		/// <summary>
		/// Tb_comment表增加一条数据
		/// </summary>
		/// <param name="tb_comment">实体对象</param>
        /// <returns></returns>
		public int AddTb_comment(Tb_comment tb_comment)
		{
			string sql =
			"INSERT Tb_comment(Infoid,contentse,cdate,isshow) "+
			"VALUES (@Infoid,@contentse,@cdate,@isshow)";
			
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
						
					new SqlParameter("@Infoid",tb_comment.Infoid),
						
					new SqlParameter("@contentse",tb_comment.Contentse),
						
					new SqlParameter("@cdate",tb_comment.Cdate),
						
					new SqlParameter("@isshow",tb_comment.Isshow)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}
		#endregion
	    #region 表删除一条数据
		/// <summary>
		/// Tb_comment表删除一条数据
		/// </summary>
		/// <param name="id"></param>
        /// <returns></returns>
			
		public int DeleteTb_commentById(int id)	
		{
			string sql="DELETE FROM "+"Tb_comment"+" Where id=@id";
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@id", id)
				};
				return DBHelper.ExecuteCommand(sql, para);
			}catch(Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}		
		#endregion
		#region 表修改数据
		/// <summary>
		/// Tb_comment表修改数据
		/// </summary>
		/// <param name="tb_comment">实体对象</param>
        /// <returns></returns>
		//考虑标识列与主键不一致情形
		//以标识列修改实体
		public int ModifyTb_comment(Tb_comment tb_comment)
		{
		string sql ="UPDATE "+"Tb_comment"+" set "+
					"Infoid=@Infoid, "+
					"contentse=@contentse, "+
					"cdate=@cdate, "+
					"isshow=@isshow "+ 
				" Where id=@id";
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@id",tb_comment.Id),
					new SqlParameter("@Infoid",tb_comment.Infoid),
					new SqlParameter("@contentse",tb_comment.Contentse),
					new SqlParameter("@cdate",tb_comment.Cdate),
					new SqlParameter("@isshow",tb_comment.Isshow)
				};
				return DBHelper.ExecuteCommand(sql, para);
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}					
		#endregion
		#region 查询数据
		#region 通过sql条件获得数据列表
	    /// <summary>
		/// Tb_comment表通过sql条件获得数据列表
		/// </summary>
		/// <param name="StrWhere"></param>
        /// <returns></returns>
		public DataSet GetDataSetFromTb_comment(string strWhere)
		{
			string strSql="SELECT * FROM Tb_comment";
			if(strWhere.Trim()!="")
			{
				strSql +=" where "+strWhere;
			}
			return DBHelper.Query(strSql.ToString());
		}
		#endregion
		#region 得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_comment表得到一个对象实体（泛型集合）
		/// </summary>
        /// <returns>IList</returns>
		public IList<Tb_comment> GetAllTb_comments()
		{
			string sqlAll = "SELECT * FROM Tb_comment";
			return GetTb_commentsBySql(sqlAll);
		}
		#endregion
		#region 得到一个对象实体（不是泛型）
	    /// <summary>
		/// Tb_comment表得到一个对象实体
		/// </summary>
		/// <param name="id"></param>
        /// <returns></returns>
		//以标识列获得对象
		public Tb_comment GetTb_commentById(int id)
		{
			
			string sql = "SELECT * FROM "+"Tb_comment"+" WHERE id = @id";
			try
			{
				SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@id", id));
				if (reader.Read())
				{
					Tb_comment tb_comment = new Tb_comment();
					if(reader["id"]!=DBNull.Value)
						tb_comment.Id=(int)reader["id"];
					if(reader["Infoid"]!=DBNull.Value)
						tb_comment.Infoid=(int)reader["Infoid"];
					if(reader["contentse"]!=DBNull.Value)
						tb_comment.Contentse=(string)reader["contentse"];
					if(reader["cdate"]!=DBNull.Value)
						tb_comment.Cdate=(DateTime)reader["cdate"];
					if(reader["isshow"]!=DBNull.Value)
						tb_comment.Isshow=(int)reader["isshow"];
					reader.Close();
					
					return tb_comment;
					}
				else
				{
					reader.Close();
					return null;
				}
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}
		#endregion
		#region 通过sql语句得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_comment表得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="safeSql">safeSql</param>
        /// <returns>IList</returns>
		public IList<Tb_comment> GetTb_commentsBySql(string safeSql)
		{
			List<Tb_comment> list = new List<Tb_comment>();
			using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					Tb_comment tb_comment = new Tb_comment();
					if(row["id"]!=DBNull.Value)
					tb_comment.Id=(int)row["id"];
					if(row["Infoid"]!=DBNull.Value)
					tb_comment.Infoid=(int)row["Infoid"];
					if(row["contentse"]!=DBNull.Value)
					tb_comment.Contentse=(string)row["contentse"];
					if(row["cdate"]!=DBNull.Value)
					tb_comment.Cdate=(DateTime)row["cdate"];
					if(row["isshow"]!=DBNull.Value)
					tb_comment.Isshow=(int)row["isshow"];
					list.Add(tb_comment);
				}
				return list;
			}
		}
		#endregion
		#region 通过sql语句的条件得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_comment表通过sql语句的条件（“ｗhere”已经写上）得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="strWhere">strWhere</param>
        /// <returns>IList</returns>
		public IList<Tb_comment> GetTb_commentsByStrWhere(string strWhere)
		{
			string safeSql="SELECT * FROM Tb_comment";
			if(strWhere.Trim()!="")
			{
				safeSql +=" where "+strWhere;
			}
			List<Tb_comment> list = new List<Tb_comment>();
			using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					Tb_comment tb_comment = new Tb_comment();
					if(row["id"]!=DBNull.Value)
					tb_comment.Id=(int)row["id"];
					if(row["Infoid"]!=DBNull.Value)
					tb_comment.Infoid=(int)row["Infoid"];
					if(row["contentse"]!=DBNull.Value)
					tb_comment.Contentse=(string)row["contentse"];
					if(row["cdate"]!=DBNull.Value)
					tb_comment.Cdate=(DateTime)row["cdate"];
					if(row["isshow"]!=DBNull.Value)
					tb_comment.Isshow=(int)row["isshow"];
					list.Add(tb_comment);
				}
				return list;
			}
		}
		#endregion
		#region 通过sql语句和参数得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_comment表通过sql语句和参数得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="safeSql">safeSql</param>
		/// <param name="SqlParameter[]">params SqlParameter[]</param>
        /// <returns>IList</returns>
		public IList<Tb_comment> GetTb_commentsBySql( string sql, params SqlParameter[] values )
		{
			List<Tb_comment> list = new List<Tb_comment>();
			try
			{
				DataTable table = DBHelper.GetDataTable( sql, values );
				
				foreach (DataRow row in table.Rows)
				{
					Tb_comment tb_comment = new Tb_comment();
					if(row["id"]!=DBNull.Value)
					tb_comment.Id=(int)row["id"];
					if(row["Infoid"]!=DBNull.Value)
					tb_comment.Infoid=(int)row["Infoid"];
					if(row["contentse"]!=DBNull.Value)
					tb_comment.Contentse=(string)row["contentse"];
					if(row["cdate"]!=DBNull.Value)
					tb_comment.Cdate=(DateTime)row["cdate"];
					if(row["isshow"]!=DBNull.Value)
					tb_comment.Isshow=(int)row["isshow"];
					list.Add(tb_comment);
				}
				return list;
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}	
		}
		#endregion
		
		#region 接收条件和参数Hashtable 返回DataTable
		/// <summary>
		/// Tb_comment表接收条件和参数Hashtable，返回DataTable
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public DataTable GetDataTableFromTb_comment(string strWhere,Hashtable table)
		{
			string strSql = "select * from Tb_comment where strWhere ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			return DBHelper.Query(strSql.ToString(), parmater.ToArray()).Tables[0];
		}
		#endregion
		#region 接收条件和参数Hashtable 返回List泛型集合
		/// <summary>
		/// Tb_comment表接收条件和参数Hashtable，返回List泛型集合
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public IList<Tb_comment> GetListFromTb_comment(string strWhere, Hashtable table)
		{
			List<Tb_comment> models = new List<Tb_comment>();
			string strSql = "select * from Tb_comment where strWhere ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString(),parmater.ToArray()))
			{
				while (dataReader.Read())
				{
					Tb_comment model = new Tb_comment();
					model.Id = Convert.ToInt32(dataReader["id"]);
					model.Infoid = Convert.ToInt32(dataReader["Infoid"]);
					model.Contentse = Convert.ToString(dataReader["contentse"]);
					model.Cdate = Convert.ToDateTime(dataReader["cdate"]);
					model.Isshow = Convert.ToInt32(dataReader["isshow"]);
					models.Add(model);
				}
			}
			return models;
		}
		#endregion
		#region 接收条件和参数Hashtable，返回一个实体类对象
		/// <summary>
		/// Tb_comment表接收条件和参数Hashtable，返回一个实体类对象
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public Tb_comment GetModelFromTb_commentByWhere(string strWhere, Hashtable table)
		{
			Tb_comment model = new Tb_comment();
			string strSql="select * from Tb_comment where "+strWhere+" ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString(), parmater.ToArray()))
			{
				while (dataReader.Read())
				{
					model.Id = Convert.ToInt32(dataReader["id"]);
					model.Infoid = Convert.ToInt32(dataReader["Infoid"]);
					model.Contentse = Convert.ToString(dataReader["contentse"]);
					model.Cdate = Convert.ToDateTime(dataReader["cdate"]);
					model.Isshow = Convert.ToInt32(dataReader["isshow"]);
				}
			}
			return model;
		}
		#endregion
		#region 接收条件，返回一个实体类对象
		/// <summary>
		/// Tb_comment表接收条件，返回一个实体类对象
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public Tb_comment GetModelFromTb_commentByWhere(string strWhere)
		{
			Tb_comment model = new Tb_comment();
			string strSql="select * from Tb_comment where "+strWhere+" ";
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString()))
			{
				while (dataReader.Read())
				{
					if(dataReader["id"]!=DBNull.Value)
					model.Id = Convert.ToInt32(dataReader["id"]);
					if(dataReader["Infoid"]!=DBNull.Value)
					model.Infoid = Convert.ToInt32(dataReader["Infoid"]);
					if(dataReader["contentse"]!=DBNull.Value)
					model.Contentse = Convert.ToString(dataReader["contentse"]);
					if(dataReader["cdate"]!=DBNull.Value)
					model.Cdate = Convert.ToDateTime(dataReader["cdate"]);
					if(dataReader["isshow"]!=DBNull.Value)
					model.Isshow = Convert.ToInt32(dataReader["isshow"]);
				}
			}
			return model;
		}
		#endregion
		
		#region 接收条件和参数Hashtable，删除符合条件的记录
		/// <summary>
		/// Tb_comment表接收条件和参数Hashtable，删除符合条件的记录
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		
		public int DeleteTb_commentByWhere(string strWhere)
		{
			string strSql="Delete From Tb_comment where "+strWhere+" ";
			return DBHelper.ExecuteCommand(strSql.ToString());
		}
		/// <summary>
		/// Tb_comment表接收条件，删除符合条件的记录
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		
		public int DeleteTb_commentByWhere(string strWhere, Hashtable table)
		{
			string strSql="Delete From Tb_comment where "+strWhere+" ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			return DBHelper.ExecuteCommand(strSql.ToString(), parmater.ToArray());
		}
		#endregion
		#endregion
		#region 分页的方法page为当前页码，NUMCount为每页数据
		
		/// <summary>
		/// 分页的方法page为当前页码，NUMCount为每页数据
		/// </summary>
		/// <param name="page">为当前页码</param>
		/// <param name="NUMCount">为每页数据</param>
        /// <returns></returns>
		public IList<Tb_comment> ShowTb_commentForPage(int curragePage,int NUMCount)
		{
			int number = 0;
			if (curragePage > 1) {
				number = NUMCount * (curragePage - 1);
			}
			string strsql= "select top "
						   +NUMCount+" * from Tb_comment where  id not in ( select top "
						   +number+" id from Tb_comment order by id desc ) order by id desc";
			return GetTb_commentsBySql(strsql);
		}
		#endregion
	}
}

